"""
Case Type   : 函数支持指定并行相关参数
Case Name   : 嵌套游标支持访问外部变量--多层嵌套
Create At   : 2024/10/12
Owner       : @kyleze
Description :
    1.创建测试表
    2.插入数据
    3.嵌套游标支持访问外部变量
    4.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
History     :
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant


class ParallelEnable(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.table_name1 = 't_cursor_0012_01'
        self.table_name2 = 't_cursor_0012_02'
        self.function_name1 = 'pro_cursor_0012_01'

    def test_paralle(self):
        step = '----step1:创建测试表 expect:成功----'
        self.log.info(step)
        create_sql = f"drop table if exists {self.table_name1};" \
                     f"drop table if exists {self.table_name2};" \
                     f"create table {self.table_name1}(department_id int, " \
                     f"department_name varchar(50));" \
                     f"create table {self.table_name2}(employees_id int, " \
                     f"department_id int, first_name varchar(50), last_name " \
                     f"varchar(50), email varchar(50), phone_number int);"
        create_res = self.primary_sh.execut_db_sql(create_sql)
        self.log.info(create_res)
        self.assertEqual(2,
                         create_res.count(self.constant.CREATE_TABLE_SUCCESS),
                         '执行失败' + step)

        step = '----step2:插入数据 expect:成功----'
        self.log.info(step)
        ins_sql = f"insert into {self.table_name1} values (1, 'sale')," \
                  f"(2, 'rd'),(3, 'pro');" \
                  f"insert into {self.table_name1} values (1, 'sale')," \
                  f"(2, 'rd'),(3, 'pro');" \
                  f"insert into {self.table_name2} values (1, 1, 'zhang', " \
                  f"'san', '1@abc.com', 123),(2, 1, 'li', 'si', '2@abc.com', " \
                  f"124);" \
                  f"insert into {self.table_name2} values (1, 1, 'wang', " \
                  f"'wu', '3@abc.com', 321),(2, 1, 'he', 'liu', '4@abc.com', " \
                  f"421);"
        ins_res = self.primary_sh.execut_db_sql(ins_sql)
        self.log.info(ins_res)
        self.assertEqual(4, ins_res.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败' + step)

        step = '----step3:嵌套游标支持访问外部变量 expect:成功----'
        self.log.info(step)
        tra_sql = f"drop procedure if exists {self.function_name1};" \
                  f"create or replace procedure {self.function_name1} as" \
                  f"    company_name varchar(100);" \
                  f"    department_name varchar(100);" \
                  f"    employees_id_var varchar(100);" \
                  f"    name_var varchar(100);" \
                  f"    type ref_cur_type is ref cursor;" \
                  f"    my_cur ref_cur_type;" \
                  f"    cursor c1 is select department_id, " \
                  f"cursor(select department_name, " \
                  f"cursor(select department_id from {self.table_name1})) " \
                  f"from {self.table_name1} d order by department_name;" \
                  f"begin" \
                  f"    open c1;" \
                  f"    loop" \
                  f"        fetch c1 into department_name, my_cur;" \
                  f"        exit when c1%notfound;" \
                  f"        raise notice 'department_name : % %'," \
                  f"department_name, my_cur;" \
                  f"        loop" \
                  f"            fetch my_cur into employees_id_var, name_var;" \
                  f"            exit when my_cur%notfound;" \
                  f"            raise notice ' department employees " \
                  f"info : % %',employees_id_var, name_var;" \
                  f"        end loop;" \
                  f"        close my_cur;" \
                  f"    end loop;" \
                  f"end;"
        fun_res = self.primary_sh.execut_db_sql(tra_sql)
        self.log.info(fun_res)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, fun_res,
                      '执行失败' + step)
        call_res = \
            self.primary_sh.execut_db_sql(f"call {self.function_name1}();")
        self.log.info(call_res)
        self.assertIn('1 row', call_res, '执行失败' + step)

    def tearDown(self):
        self.log.info('----清理环境----')
        table_res = self.primary_sh.execut_db_sql(
            f'drop table {self.table_name1}, {self.table_name2};')
        self.log.info(table_res)
        pro_res = self.primary_sh.execut_db_sql(
            f'drop procedure {self.function_name1};')
        self.log.info(pro_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, table_res, '删表失败')
        self.assertIn(
            self.constant.DROP_PROCEDURE_SUCCESS_MSG, pro_res, '删表失败')
        self.log.info(f'----{os.path.basename(__file__)} end----')
